package com.tistory.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tistory.credit.vo.CreditReq;


public class XlsReadBank {

	private static final Logger log = LoggerFactory.getLogger(XlsReadBank.class);
	public static List<CreditReq> parsingExcel(String filepath,String userId){

		List<CreditReq>creditList = new ArrayList<CreditReq>();
		try{
			HSSFWorkbook workBook  =  new HSSFWorkbook(new FileInputStream(new File(filepath)));

			int sheetNum	= 0;
			int rowsCnt 	= 0;
			int cellsCnt 	= 0;
			HSSFSheet sheet = null;
			HSSFRow row 	= null;
			HSSFCell cell 	= null;
			String value = "";

			HashMap<String , Object> params;

			sheetNum    = workBook.getNumberOfSheets();

			log.info("\n# sheet num : " + sheetNum);

			for( int loop = 0; loop < sheetNum; loop++){			
				sheet = workBook.getSheetAt(loop);			 
				rowsCnt = sheet.getPhysicalNumberOfRows();

				for( int rownum = 1; rownum < rowsCnt; rownum++){
					CreditReq req = new CreditReq();
					row = sheet.getRow(rownum);

					log.info("\n# row num : " + rownum);

					if(row != null){
						cellsCnt = row.getPhysicalNumberOfCells();

						for(int c =0; c < cellsCnt; c++){
							params = new HashMap<String , Object>();

							value = "";
							cell = row.getCell(c);

							if(cell != null){

								switch (cell.getCellType()) {

								case XSSFCell.CELL_TYPE_FORMULA:				            	
									params.put("CELL_TYPE_FORMULA"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_STRING:
									params.put("CELL_TYPE_STRING"+c, cell.getStringCellValue());				            	
									value = cell.getStringCellValue();
									break;

								case XSSFCell.CELL_TYPE_BLANK:
									params.put("CELL_TYPE_BLANK"+c, cell.getBooleanCellValue());
									break;

								case XSSFCell.CELL_TYPE_ERROR :
									params.put("CELL_TYPE_ERROR"+c, cell.getErrorCellValue());
									break;

								case XSSFCell.CELL_TYPE_NUMERIC :				            	
									params.put("CELL_TYPE_NUMERIC"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_BOOLEAN :
									params.put("CELL_TYPE_BOOLEAN"+c, cell.getBooleanCellValue());
									break;

								default:
									break;
								}

								switch (c) {
								case 0:
									req.setUserId(userId);
									if(value.indexOf("승인")>-1){
										req.setOkFlag("00");
									}else{
										req.setOkFlag("99");
									}
									break;
								case 1:
									req.setTransDate(value);
									break;
								case 2:
									req.setDeviceNum(value);
									break;
								case 3:
									req.setDeviceName(value);
									break;
								case 5:
									req.setCardNum(value);
									break;
								case 6:
									if(value.indexOf("신용")>-1){
										req.setCardFlag("10");	
									}else if(value.indexOf("체크")>-1){
										req.setCardFlag("20");
									}else{
										req.setCardFlag("30");
									}
									
									break;									
								case 8:
									req.setCardCom(value);
									String expectTransDate = DateUtil.deleteDash(req.getTransDate().substring(0,10));
									if(value.indexOf("비씨")>-1){
										req.setCardComCd("10");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 3));
									}else if (value.indexOf("국민")>-1){
										req.setCardComCd("11");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 4));
									}else if (value.indexOf("외환")>-1){
										req.setCardComCd("12");
									}else if (value.indexOf("삼성")>-1){
										req.setCardComCd("13");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 5));
									}else if (value.indexOf("현대")>-1){
										req.setCardComCd("15");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 4));
									}else if (value.indexOf("롯데")>-1){
										req.setCardComCd("16");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 3));
									}else if (value.indexOf("신한카드")>-1){
										req.setCardComCd("17");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 8));
									}else if (value.indexOf("전북")>-1){
										req.setCardComCd("18");
									}else if (value.indexOf("하나")>-1){
										req.setCardComCd("19");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 4));
									}else if (value.indexOf("농협")>-1){
										req.setCardComCd("20");
										req.setExpectTransDate(getDayOfWeek(expectTransDate , 3));
									}else{
										req.setCardComCd("21");
									}

									break;
								case 9:
									req.setChainNum(value);
									break;
								case 10:
									if(req.getCardComCd().equals("16")|| req.getCardComCd().equals("19")){
										req.setProdAmt("0");
									}else{
										req.setProdAmt(value);
									}
									break;
								case 12:
									req.setOkNum(value);
									break;
								case 14:
									req.setCancelDate(value);
									break;
								case 16:
									if(value.indexOf("정상완료")>-1){
										req.setCardResCode("00");
									}else{
										req.setCardResCode("99");
									}									
									break;
								default:
									break;
								}								
							} 

						} // for(c) 문
					}
					creditList.add(req);
				} // for(r) 문
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return creditList;
	}

	private static String getDayOfWeek (String today , int differDay) throws Exception{

		String dayOfWeek = DateUtil.getDayOfWeek(today);
		String expectTransDate ="";
		if(differDay ==3){
			if(dayOfWeek.equals("수") || dayOfWeek.equals("목") || dayOfWeek.equals("금")  ){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+2);
			}else	if(dayOfWeek.equals("토")){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+1);
			}else {	
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay);
			}
		}else if(differDay ==4){
			if(dayOfWeek.equals("수") || dayOfWeek.equals("화") || dayOfWeek.equals("목") || dayOfWeek.equals("금")  ){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+2);
			}else	if(dayOfWeek.equals("토")){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+1);
			}else {	
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,4);
			}
		}else if(differDay ==5){
			if(dayOfWeek.equals("화") ||dayOfWeek.equals("수")  ||dayOfWeek.equals("목")|| dayOfWeek.equals("금") ){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,6);
			}else if( dayOfWeek.equals("화")   ){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,5);
			}else{
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,4);
			}

		}else if(differDay ==8){
			if(dayOfWeek.equals("금")  ){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+2);
			}else	if(dayOfWeek.equals("토")){
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay+1);
			}else {	
				expectTransDate = DateUtil.getDayAfterWithOutSlash(today,differDay);
			}
		}
		return DateUtil.dashDate(expectTransDate);
	}

}